Working with Messy Data

Christopher Teixeira

A little about me…


Christopher Teixeira

Principal Data Scientist

The MITRE Corporation



Interests

  • Data Analytics
  • Applied Statistics
  • Operations Research

Education

  • MS in Operations Research, George Mason University
  • BSc in Mathematics, Worcester Polytechnic Institute

Reading in data

Code 
url <- "https://data.cms.gov/data-api/v1/dataset/8889d81e-2ee7-448f-8713-f071038289b5/data?size=5000"
df <- jsonlite::fromJSON(url)
knitr::kable(head(df), format="html")
Rndrng_NPI Rndrng_Prvdr_Last_Org_Name Rndrng_Prvdr_First_Name Rndrng_Prvdr_MI Rndrng_Prvdr_Crdntls Rndrng_Prvdr_Gndr Rndrng_Prvdr_Ent_Cd Rndrng_Prvdr_St1 Rndrng_Prvdr_St2 Rndrng_Prvdr_City Rndrng_Prvdr_State_Abrvtn Rndrng_Prvdr_State_FIPS Rndrng_Prvdr_Zip5 Rndrng_Prvdr_RUCA Rndrng_Prvdr_RUCA_Desc Rndrng_Prvdr_Cntry Rndrng_Prvdr_Type Rndrng_Prvdr_Mdcr_Prtcptg_Ind Tot_HCPCS_Cds Tot_Benes Tot_Srvcs Tot_Sbmtd_Chrg Tot_Mdcr_Alowd_Amt Tot_Mdcr_Pymt_Amt Tot_Mdcr_Stdzd_Amt Drug_Sprsn_Ind Drug_Tot_HCPCS_Cds Drug_Tot_Benes Drug_Tot_Srvcs Drug_Sbmtd_Chrg Drug_Mdcr_Alowd_Amt Drug_Mdcr_Pymt_Amt Drug_Mdcr_Stdzd_Amt Med_Sprsn_Ind Med_Tot_HCPCS_Cds Med_Tot_Benes Med_Tot_Srvcs Med_Sbmtd_Chrg Med_Mdcr_Alowd_Amt Med_Mdcr_Pymt_Amt Med_Mdcr_Stdzd_Amt Bene_Avg_Age Bene_Age_LT_65_Cnt Bene_Age_65_74_Cnt Bene_Age_75_84_Cnt Bene_Age_GT_84_Cnt Bene_Feml_Cnt Bene_Male_Cnt Bene_Race_Wht_Cnt Bene_Race_Black_Cnt Bene_Race_API_Cnt Bene_Race_Hspnc_Cnt Bene_Race_NatInd_Cnt Bene_Race_Othr_Cnt Bene_Dual_Cnt Bene_Ndual_Cnt Bene_CC_AF_Pct Bene_CC_Alzhmr_Pct Bene_CC_Asthma_Pct Bene_CC_Cncr_Pct Bene_CC_CHF_Pct Bene_CC_CKD_Pct Bene_CC_COPD_Pct Bene_CC_Dprssn_Pct Bene_CC_Dbts_Pct Bene_CC_Hyplpdma_Pct Bene_CC_Hyprtnsn_Pct Bene_CC_IHD_Pct Bene_CC_Opo_Pct Bene_CC_RAOA_Pct Bene_CC_Sz_Pct Bene_CC_Strok_Pct Bene_Avg_Risk_Scre
1003000126 Enkeshafi Ardalan M.D. M I 6410 Rockledge Dr Ste 304 Bethesda MD 24 20817 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US Internal Medicine Y 36 661 3749 515976.55 289822.37 231289.23 198266.66 * # 78 42 197 247 175 378 283 469 83 62 14 0 33 188 473 0.24 0.3 0.12 0.16 0.39 0.52 0.24 0.37 0.44 0.75 0.75 0.62 0.11 0.58 0.06 0.14 1.8026
1003000134 Cibull Thomas L M.D. M I 2650 Ridge Ave Evanston Hospital Evanston IL 17 60201 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US Pathology Y 21 3216 7359 1136431.94 273928.38 203094.28 191013.47 0 0 0 0 0 0 0 21 3216 7359 1136431.94 273928.38 203094.28 191013.47 76 88 1396 1198 534 1698 1518 2920 28 66 47 0 155 190 3026 0.1 0.07 0.05 0.14 0.11 0.24 0.06 0.15 0.2 0.52 0.51 0.24 0.11 0.38 0.01 0.03 1.0785
1003000142 Khalil Rashid M.D. M I 4126 N Holland Sylvania Rd Suite 220 Toledo OH 39 43623 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US Anesthesiology Y 52 239 1932 295950.73 122731.08 93430.64 95509.35 5 32 898 8254.52 2818.25 2306.71 3229.16 47 239 1034 287696.21 119912.83 91123.93 92280.19 68 77 98 52 12 147 92 171 55 0 74 165 0.09 0.06 0.09 0.11 0.21 0.37 0.21 0.39 0.38 0.58 0.7 0.33 0.09 0.75 0.06 1.492
1003000423 Velotta Jennifer A M.D. F I 11100 Euclid Ave Cleveland OH 39 44106 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US Obstetrics & Gynecology Y 19 69 738 21300 8017.57 6735.97 6892.83 * # 66 47 69 0 14 55 0.16 0.28 0.17 0.55 0.46 0.16 0.17 0.38 0.6362
1003000480 Rothchild Kevin B MD M I 12605 E 16th Ave Aurora CO 08 80045 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US General Surgery Y 29 112 162 180891 35872.83 28071.92 27509.07 0 0 0 0 0 0 0 29 112 162 180891 35872.83 28071.92 27509.07 65 39 55 64 48 82 15 0 46 66 0.18 0.21 0.35 0.13 0.32 0.4 0.48 0.65 0.31 0.12 0.59 1.8233
1003000530 Semonche Amanda M DO F I 1021 Park Ave Suite 203 Quakertown PA 42 18951 1 Metropolitan area core: primary flow within an urbanized area of 50,000 and greater US Internal Medicine Y 31 404 1487 257901 180753.38 140941.85 129609.8 5 106 120 14114 10903.23 10903.23 10684.71 26 404 1367 243787 169850.15 130038.62 118925.09 72 50 206 110 38 237 167 383 51 353 0.08 0.07 0.06 0.11 0.11 0.29 0.1 0.27 0.25 0.74 0.69 0.22 0.07 0.33 0.03 0.04 1.1156

Changing data types

strings to numbers using factors

Exploratory data analysis

Exploratory data analysis (EDA) is used by data scientists to analyze and investigate data sets and summarize their main characteristics, often employing data visualization methods. It helps determine how best to manipulate data sources to get the answers you need, making it easier for data scientists to discover patterns, spot anomalies, test a hypothesis, or check assumptions. 1(https://www.ibm.com/topics/exploratory-data-analysis)

Four primary types of EDA:

  1. Univariate non-graphical
  2. Univariate graphical
  3. Multivariate nongraphical
  4. Multivariate graphical

Applying EDA: Univariate

Code 
skimr::skim(df)
Data summary
Name df
Number of rows 5000
Number of columns 73
_______________________
Column type frequency:
character 73
________________________
Group variables None

Variable type: character

skim_variable n_missing complete_rate min max empty n_unique whitespace
Rndrng_NPI 0 1 10 10 0 5000 0
Rndrng_Prvdr_Last_Org_Name 0 1 2 58 0 3966 0
Rndrng_Prvdr_First_Name 0 1 0 17 183 2016 0
Rndrng_Prvdr_MI 0 1 0 1 1996 27 0
Rndrng_Prvdr_Crdntls 0 1 0 20 605 379 0
Rndrng_Prvdr_Gndr 0 1 0 1 183 3 0
Rndrng_Prvdr_Ent_Cd 0 1 1 1 0 2 0
Rndrng_Prvdr_St1 0 1 7 55 0 4322 0
Rndrng_Prvdr_St2 0 1 0 55 3878 751 0
Rndrng_Prvdr_City 0 1 2 23 0 1699 0
Rndrng_Prvdr_State_Abrvtn 0 1 2 2 0 53 0
Rndrng_Prvdr_State_FIPS 0 1 2 2 0 53 0
Rndrng_Prvdr_Zip5 0 1 5 5 0 2809 0
Rndrng_Prvdr_RUCA 0 1 0 4 1 20 0
Rndrng_Prvdr_RUCA_Desc 0 1 0 100 1 15 0
Rndrng_Prvdr_Cntry 0 1 2 2 0 1 0
Rndrng_Prvdr_Type 0 1 7 48 0 86 0
Rndrng_Prvdr_Mdcr_Prtcptg_Ind 0 1 1 1 0 2 0
Tot_HCPCS_Cds 0 1 1 3 0 182 0
Tot_Benes 0 1 2 4 0 958 0
Tot_Srvcs 0 1 2 7 0 2075 0
Tot_Sbmtd_Chrg 0 1 3 11 0 4980 0
Tot_Mdcr_Alowd_Amt 0 1 4 10 0 4997 0
Tot_Mdcr_Pymt_Amt 0 1 3 10 0 5000 0
Tot_Mdcr_Stdzd_Amt 0 1 3 10 0 4997 0
Drug_Sprsn_Ind 0 1 0 1 4402 2 0
Drug_Tot_HCPCS_Cds 0 1 0 3 598 37 0
Drug_Tot_Benes 0 1 0 3 598 217 0
Drug_Tot_Srvcs 0 1 0 6 598 554 0
Drug_Sbmtd_Chrg 0 1 0 11 598 882 0
Drug_Mdcr_Alowd_Amt 0 1 0 10 598 890 0
Drug_Mdcr_Pymt_Amt 0 1 0 10 598 889 0
Drug_Mdcr_Stdzd_Amt 0 1 0 10 598 890 0
Med_Sprsn_Ind 0 1 0 1 4402 3 0
Med_Tot_HCPCS_Cds 0 1 0 3 598 175 0
Med_Tot_Benes 0 1 0 4 598 920 0
Med_Tot_Srvcs 0 1 0 7 598 1843 0
Med_Sbmtd_Chrg 0 1 0 11 598 4388 0
Med_Mdcr_Alowd_Amt 0 1 0 10 598 4402 0
Med_Mdcr_Pymt_Amt 0 1 0 10 598 4403 0
Med_Mdcr_Stdzd_Amt 0 1 0 10 598 4400 0
Bene_Avg_Age 0 1 2 2 0 57 0
Bene_Age_LT_65_Cnt 0 1 0 4 2011 262 0
Bene_Age_65_74_Cnt 0 1 0 4 792 566 0
Bene_Age_75_84_Cnt 0 1 0 4 1442 489 0
Bene_Age_GT_84_Cnt 0 1 0 4 2170 313 0
Bene_Feml_Cnt 0 1 0 4 613 676 0
Bene_Male_Cnt 0 1 0 4 613 587 0
Bene_Race_Wht_Cnt 0 1 0 4 1592 853 0
Bene_Race_Black_Cnt 0 1 0 4 3332 246 0
Bene_Race_API_Cnt 0 1 0 3 4094 112 0
Bene_Race_Hspnc_Cnt 0 1 0 3 3665 196 0
Bene_Race_NatInd_Cnt 0 1 0 3 3348 43 0
Bene_Race_Othr_Cnt 0 1 0 3 4139 93 0
Bene_Dual_Cnt 0 1 0 4 1321 390 0
Bene_Ndual_Cnt 0 1 0 4 1321 822 0
Bene_CC_AF_Pct 0 1 0 4 1577 66 0
Bene_CC_Alzhmr_Pct 0 1 0 4 1623 76 0
Bene_CC_Asthma_Pct 0 1 0 4 2008 42 0
Bene_CC_Cncr_Pct 0 1 0 4 1623 71 0
Bene_CC_CHF_Pct 0 1 0 4 1208 75 0
Bene_CC_CKD_Pct 0 1 0 4 745 73 0
Bene_CC_COPD_Pct 0 1 0 4 1452 68 0
Bene_CC_Dprssn_Pct 0 1 0 4 751 74 0
Bene_CC_Dbts_Pct 0 1 0 4 753 72 0
Bene_CC_Hyplpdma_Pct 0 1 0 4 398 67 0
Bene_CC_Hyprtnsn_Pct 0 1 0 4 299 62 0
Bene_CC_IHD_Pct 0 1 0 4 774 71 0
Bene_CC_Opo_Pct 0 1 0 4 1810 46 0
Bene_CC_RAOA_Pct 0 1 0 4 528 70 0
Bene_CC_Sz_Pct 0 1 0 4 2872 70 0
Bene_CC_Strok_Pct 0 1 0 4 2339 68 0
Bene_Avg_Risk_Scre 0 1 1 7 0 4438 0

pyskim

Applying EDA: Multivariate

pyskim

Working with missing data

Questions to ask when working with missing data

  • Does “missing” mean something different from “0”?
  • Is “missing” captured in another way?
  • Was there a change in how data was being captured?
  • Does it make sense to replacing missing values?

Imputing missing data

There are two general approaches:

  • Overly simple approach: replace missing values with mean, median, or mode
  • Sophisticated approach: replacing missing values by analyzing the full dataset

Multivariate Imputation by Chained Equations (MICE)

Cautionary tales in working with data

Data drift

Changes in the data can happen over time, resulting in “data drift” that can impact model performance or other decisions that can be overlooked if only near term changes are considered.

Four different types of data drift described visually.

Cognitive biases

Cognitive biases are systematic patterns of deviation from norm and/or rationality in judgment. They are often studied in psychology, sociology and behavioral economics.1

Some biases to be aware of:

  • Survivorship bias: Analyzing just the data that is available without analyzing the larger situation.
  • False causality: Seeing correlation between two variables does not imply one causes the other to occur. 2
  • Availability bias: Drawing conclusions on limited data.
  • Confirmation bias: Manipulating data to confirm your own hypothesis.

Simpson’s paradox

Simpson’s paradox occurs when groups of data show one particular trend, but this trend is reversed when the groups are combined together. Understanding and identifying this paradox is important for correctly interpreting data. 1



A baseball player can have higher batting average than another on each of two years, but lower than the other when the two are combined. In one case, David Justice had a higher batting average than Derek Jeter in 1995 and 1996, but across the two years, Jeter’s average was higher. 2

Picture explaining Simpson's paradox.

Goodhart’s law

Any observed statistical regularity will tend to collapse once pressure is placed upon it for control purposes. 1



or a better way of putting it is:



When a measure becomes a target, it ceases to be a good measure. 2

Sketchplanations cartoon explaining Goodhart's Law

Sharing your data with others

Reproducibility and Replicability

Reproducibility is the ability of independent investigators to draw the same conclusions from an experiment by following the documentation shared by the original investigators. Hence, reproducibility requires that another, independent team of investigators have to conduct the same experiment. 1

Easy tips for enabling others to reproduce your work:

  • Use a static random seed
    • in R: set.seed(42)
    • in Python: random.seed(42)
  • Document your environment
    • in R: library(renv)
    • in Python: pip freeze > requirements.txt
  • Use version control (e.g., Github, Bitbucket)
  • Use notebooks

Using pins

The pins package publishes data, models, and other R objects, making it easy to share them across projects and with your colleagues. You can pin objects to a variety of pin boards, including folders (to share on a networked drive or with services like DropBox), Posit Connect, Amazon S3, Google Cloud Storage, Azure storage, and Microsoft 365 (OneDrive and SharePoint). Pins can be automatically versioned, making it straightforward to track changes, re-run analyses on historical data, and undo mistakes. 1

Pins is available in R and Python.

Discussion / Contact Info



Christopher Teixeira

christopherteixeira.com



chris@christopherteixeira.com

in/christopherteixeira

ct-analytics

Christopher Teixeira